﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Threading;
using System.IO;
using System.IO.Ports;
using Microsoft.Office.Core;
using Excel = Microsoft.Office.Interop.Excel;

namespace SCA_Check_ExcelFile
{
    public partial class SCA_Check_ExcelFile
    {
        public bool Check_Excel_File(string file_name)
        {
            int row, col;
            string cell_val;
            int i = 0;
            Excel.Worksheet xlSheet;
            int error_num = 0;

            OpenWB = Open_excel_file(File_Name, "");
            xlSheet = (Excel.Worksheet)OpenWB.Worksheets.get_Item(1);

            row = 17;
            col = 1;
            cell_val = Get_Excel_Line((Excel.Worksheet)OpenWB.Sheets[1], row, col, 1).Trim();
            if (cell_val != "Line" ) 
            {
                ((Excel.Range)xlSheet.Cells[row, col]).Interior.Color = 255;    // Red: 255, yellow: 65535, orange: 49407
                error_num++;
                MessageBox.Show("Wrong Starr Line", "Error");
                return false;
            }

            StatusLabel1.Text = "Checking File";
            while (cell_val != "")
            {
                col++;
                cell_val = Get_Excel_Line((Excel.Worksheet)OpenWB.Sheets[1], row, col, 1).Trim();
            }
            Max_Col = col - 1;

            // Check Max Row
            row = 18;
            col = 2;
            cell_val = Get_Excel_Line((Excel.Worksheet)OpenWB.Sheets[1], row, col, 1).Trim();
            while (cell_val != "")
            {
                row++;
                cell_val = Get_Excel_Line((Excel.Worksheet)OpenWB.Sheets[1], row, col, 1).Trim();
            }
            Max_Row = row - 1;

            ProgressBar1.Visible = true;
            for (row = 18; row <= Max_Row; row++)
            {
                for (col = 5; col <= Max_Col; col++)
                {
                    cell_val = Get_Excel_Line((Excel.Worksheet)OpenWB.Sheets[1], row, col, 1).Trim();
                    if (cell_val == "")
                    {
                        ((Excel.Range)xlSheet.Cells[row, col]).Interior.Color = 65535;
                    }
                    else
                    {
                        if (Check_Valid_Cell(cell_val) == false)
                        {
                            ((Excel.Range)xlSheet.Cells[row, col]).Interior.Color = 255;
                            // xlSheet.Cells[row, col] = "E";
                            error_num++;
                        }
                        
                    }
                    // Update progress Bar
                    i++;
                    ProgressBar1.Value = i % 100;
                }
            }
            ProgressBar1.Visible = false;
            StatusLabel1.Text = "DONE";

            if (error_num != 0)
            {
                MessageBox.Show("Total Error = " + error_num, "Error");
            }
            else {
                MessageBox.Show("Success with no error" + error_num, "Success");
            }
            
            Close_WorkBook(OpenWB);

            return true;
        }

        public bool Check_Valid_Cell(string cell_var)
        {
            int total;
            string check_st;

            total = Valid_Code.Count();
            foreach (string st in Valid_Code)
            {
                if (cell_var == st)
                {
                    return true;
                }

                check_st = "O" + st;
                if (cell_var == check_st)
                {
                    return true;
                }

                check_st = "1" + st;
                if (cell_var == check_st)
                {
                    return true;
                }
                check_st = "2" + st;
                if (cell_var == check_st)
                {
                    return true;
                }
                check_st = "3" + st;
                if (cell_var == check_st)
                {
                    return true;
                }
            }
            return false;
        }
    }
}